# -*- coding: UTF-8 -*-

import pandas as pd
from datetime import datetime
from config import config
import numpy as np

import matplotlib.pyplot as plt

import seaborn as sns

from dash_lib.mongodb_helper import mongo_db, get_email_tipper_list_from_online, get_tipper_list_from_online

pd.options.display.float_format = '{:,.1f}'.format
pd.set_option('display.height', 1000)
pd.set_option('display.max_rows', 500)
pd.set_option('display.max_columns', 700)
pd.set_option('display.width', 1000)


def init(blogname):
    df = pd.read_excel(open(config.get_excel_name_s(blogname, 'a'), 'rb'), sheet_name='sheet1',
                       header=None,
                       names=['match', 'pick', 'odd', 'stake', 'pick_type', 'profit', 'result', 'sport', 'category',
                              'kickoff', 'kickoff_time'])
    pd.options.display.float_format = '{:,.1f}'.format
    pd.set_option('display.height', 1000)
    pd.set_option('display.max_rows', 500)
    pd.set_option('display.max_columns', 700)
    pd.set_option('display.width', 1000)

    return df


def trans_date(kickoff):
    # format_kickoff= datetime.strptime(kickoff, '%d %b %Y, %H:%M').time()
    try:
        format_kickoff = datetime.strptime(kickoff, '%d %b %Y, %H:%M').strftime('%Y-%m-%d')
    except TypeError:
        format_kickoff = '2000-01-11'

    return format_kickoff


def indexDay(df):
    pivot_table = pd.pivot_table(df, values=['profit', ], index=['kickoff', 'sport'], columns=['stake', 'pick_type'],
                                 aggfunc={'profit': 'mean'}, fill_value=0, margins=True)

    print(pivot_table)


def show_plt(df, blogname):
    sns.set_style("whitegrid")
    df.plot()
    plt.title(blogname)
    plt.show(block=False)


def analyse_base(df):
    # ('len', 'profit')
    # ('mean', 'profit')
    # ('sum', 'profit')
    # ('median', 'profit')
    # ('amax', 'profit')
    base_pivot_table = pd.pivot_table(df, values=['profit'], index=['pick_type', 'sport', 'stake'],
                                      aggfunc=[len, np.mean, np.sum, np.median, np.max], fill_value=0, margins=False)

    _pivot_table = pd.pivot_table(df, values=['profit'], index=['stake'],
                                  aggfunc=[len, np.mean, np.sum, np.median, np.max], fill_value=0, margins=False)

    pivot_table = pd.pivot_table(df, values=['profit', 'stake'], index=['kickoff'],
                                 aggfunc={'profit': 'sum', 'stake': 'sum'}, fill_value=0, margins=False)
    pivot_table['yield'] = pivot_table['profit'] / pivot_table['stake'] * 100
    print(pivot_table.columns.values)

    print(pivot_table.tail(15))
    cumsum_df = pivot_table.cumsum()
    print_day_duration(df, len(cumsum_df['stake']))
    cumsum_df['yield'] = cumsum_df['profit'] / cumsum_df['stake'] * 100
    cumsum_df['count'] = len(cumsum_df)

    print(cumsum_df.round(2).tail(1))
    return cumsum_df


def print_day_duration(df, days_count):
    print('DAY:   %s ==> %s     (days:%s ,count:%d)' %
          (df['kickoff'].min(), df['kickoff'].max(), days_count, len(df['kickoff'])))


def show_yield(cumsum_df):
    # print('========  show yield     ========'
    cumsum_df['yield'] = cumsum_df['profit'] / cumsum_df['stake'] * 100
    print(cumsum_df.round(2).tail(1))


def filter(df, query_array):
    filtered_df = df
    if query_array:
        for item in query_array:
            filtered_df = filtered_df.query(item)
    return filtered_df


def round(df):
    return df.round(2)


def analyse(blog_name, show_plot_type='Combo'):
    find_list = list(
        mongo_db.pick.find({'kickoff': {'$gte': '2018-05-03'}, 'tipper': blog_name}))
    if not find_list:
        return
    print(' ', blog_name, ' ', len(find_list), ' #########')
    df = pd.DataFrame(find_list)
    df = filter_max(df)
    query_array = []
    # query_array.append('pick_type==["Single"]')
    cumsum_df = analyse_base(df)
    # plt.show()


def filter_max(df):
    return df.query('profit/stake<=5')


def analyse_comsum():
    find_list = list(
        mongo_db.pick.find({'kickoff': {'$gte': '2018-05-01'}, 'tipper': {'$in': get_email_tipper_list_from_online()}}))
    print(len(find_list))

    df = pd.DataFrame(find_list)
    df = filter_max(df)
    day_per_df = pd.pivot_table(df, values=['profit', 'stake', 'kickoff'], index=['tipper'],
                                aggfunc={'profit': 'sum', 'stake': 'sum', 'kickoff': 'max'}, fill_value=0,
                                margins=False)
    print(day_per_df.columns.values)
    # day_per_df['yield'] = day_per_df[('sum', 'profit')] / day_per_df[('sum', 'stake')] * 100
    day_per_df['yield'] = day_per_df['profit'] / day_per_df[('stake')] * 100

    print(round(day_per_df))
    cumsum_df = day_per_df.cumsum()

    # cumsum_df['yield'] = cumsum_df[('sum', 'profit')] / cumsum_df[('sum', 'stake')] * 100
    cumsum_df['yield'] = cumsum_df['profit'] / cumsum_df[('stake')] * 100

    print(round(cumsum_df).tail(1))


def filter_not_update():
    find_list = list(
        mongo_db.pick.find())
    df = pd.DataFrame(find_list)
    day_per_df = pd.pivot_table(df, values=['profit', 'stake', 'kickoff'], index=['tipper'],
                                aggfunc={'profit': 'sum', 'stake': 'sum', 'kickoff': 'max'}, fill_value=0,
                                margins=False)
    print(day_per_df.columns.values)
    # day_per_df['yield'] = day_per_df[('sum', 'profit')] / day_per_df[('sum', 'stake')] * 100
    day_per_df['yield'] = day_per_df['profit'] / day_per_df[('stake')] * 100
    day_per_df = day_per_df.sort_values(['kickoff'], ascending=False)

    print(round(day_per_df))


def get_tipper_mark_rate():
    find = mongo_db.rate.find()
    distinct = find.distinct("tipper")
    print(len(distinct), distinct)
    return distinct


#
# def get_rate_limit(tipper):
#     tipper_list = get_tipper_mark_rate()
#     tipper_rates = next(x for x in tipper_list if tipper in tipper_list)
#     print(tipper_rates)

def get_rate_limit(tipper):
    tipper_rates = list(mongo_db.pick.find({'tipper': tipper}))
    print(tipper_rates)
    return tipper_rates


def _filter(df, query_array):
    filtered_df = df
    if query_array:
        for item in query_array:
            filtered_df = filtered_df.query(item)
    return filtered_df


def format_quert(limit):
    _query = {'tipper': limit['tipper'],
              '$and': [{'stake': {'$lte': limit['stake'][1]}}, {'stake': {'$gte': limit['stake'][0]}}]}

    if limit['sport'] == 'All' and limit['pick_type'] == 'All':
        pass
    elif limit['sport'] == 'All':
        _query = {'pick_type': limit['pick_type']}
    elif limit['pick_type'] == 'All':
        _query = {'sport': limit['sport']}
    return _query


def format_rate_db():
    limits = list(mongo_db.rate.find())
    total_find_list = []
    for limit in limits:
        found = list(mongo_db.pick.find(format_quert(limit)))

        for find_item in found:
            fixed_stake = limit['score']
            fixed_profit = find_item['profit'] / find_item['stake'] * limit['score']
            mongo_db.pick.update_one({'_id': find_item['_id']},
                                     {'$set': {'fixed_profit': fixed_profit, 'fixed_stake': fixed_stake}})

    for limit in limits:
        found = list(mongo_db.pick.find(format_quert(limit)))
        total_find_list.extend(found)

    df = pd.DataFrame(total_find_list)

    df = filter_df(df)
    day_per_df = pd.pivot_table(df, values=['fixed_profit', 'fixed_stake'], index=['tipper'],
                                aggfunc={'fixed_profit': 'sum', 'fixed_stake': 'sum'}, fill_value=0,
                                margins=False)
    print(day_per_df.columns.values)
    # day_per_df['yield'] = day_per_df[('sum', 'profit')] / day_per_df[('sum', 'stake')] * 100
    day_per_df['yield'] = day_per_df['fixed_profit'] / day_per_df['fixed_stake'] * 100
    print(round(day_per_df))

    df_cumsum = day_per_df.cumsum()
    df_cumsum['yield'] = df_cumsum['fixed_profit'] / df_cumsum['fixed_stake'] * 100

    print(round(df_cumsum.tail(1)))


def filter_df(df):
    query_array = []
    query_array.append('profit/stake<5')
    query_array.append('kickoff>"2018-05-15"')
    # query_array.append('tipper!="6ix9ine"')
    # query_array.append('tipper!="nousnou"')
    # query_array.append('book_maker==["PINNACLE"]')
    return _filter(df, query_array)


format_rate_db()
